package com.sxj.corejava.code13_JDBC;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class Test09_练习 {

    public static void main(String[] args) throws DataAccessException {
        // 1.保存班级以及该班中的所有学生信息
//        ClassBean classBean = new ClassBean("wbs25001","计算机科学与技术");
//        Integer id = insertReturnPrimaryKey(classBean);
//        classBean.setId(id);
//        Student student = new Student("zj25001","admin",22,classBean);
//        Student student2 = new Student("zj25002","lucy",21,classBean);
//        Student student3 = new Student("zj25003","jack",23,classBean);
//        insertStudent(student);
//        insertStudent(student2);
//        insertStudent(student3);

        // 2.根据班级号查询指定班级以及该班级中所有学生信息
//        ClassBean classBean = selectByNo("wbs25001");
//        ClassBean classBean = selectByNo("wbs25002");
//        ClassBean classBean = selectByNo("wbs25003");
//        System.out.println(classBean);

        // 3.查询所有学生以及其所属班级信息
        List<Student> students = selectAllStudents();
        for (Student student : students) {
            System.out.println(student);
        }
    }


    /**
     * 保存班级信息并返回自动生成的主键值
     * @param classBean
     */
    public static Integer insertReturnPrimaryKey(ClassBean classBean) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        Integer id = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_class ")
                    .append("   (no,major)")
                    .append(" values ")
                    .append("   (?,?)")
                    .toString();
            ps = conn.prepareStatement(sql, PreparedStatement.RETURN_GENERATED_KEYS);
            ps.setString(1, classBean.getNo());
            ps.setString(2, classBean.getMajor());
            ps.executeUpdate();
            rs = ps.getGeneratedKeys();
            while (rs.next()) {
                id = rs.getInt(1);
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return id;
    }

    /**
     * 保存学生信息
     * @param student
     */
    public static void insertStudent(Student student) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" insert into ")
                    .append(" t_student")
                    .append("   (no,name,age,class_id)")
                    .append(" values ")
                    .append("   (?,?,?,?)")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, student.getNo());
            ps.setString(2, student.getName());
            ps.setInt(3, student.getAge());
            ps.setInt(4, student.getClassBean().getId());
            ps.executeUpdate();
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, null);
        }
    }

    /**
     * 根据班级号查询指定班级以及该班级中所有学生信息
     * @param classNo
     */
    public static ClassBean selectByNo(String classNo) throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        ClassBean classBean = null;
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select c.id,c.no,c.major,s.id 's.id',s.no 's.no',s.name,s.age ")
                    .append(" from t_class c ")
                    .append(" left join t_student s ")
                    .append(" on c.id = s.class_id ")
                    .append(" where c.no = ? ")
                    .toString();
            ps = conn.prepareStatement(sql);
            ps.setString(1, classNo);
            rs = ps.executeQuery();
            while (rs.next()) {
                // 一方数据映射
                if (classBean == null) {
                    classBean = new ClassBean();
                    classBean.setId(rs.getInt("id"));
                    classBean.setNo(rs.getString("no"));
                    classBean.setMajor(rs.getString("major"));
                }

                // 多方数据映射
                Student student = new Student();
                student.setId(rs.getInt("s.id"));
                student.setNo(rs.getString("s.no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));

                classBean.addStudent(student);
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return classBean;
    }

    /**
     * 查询所有学生以及其所属班级信息
     */
    public static List<Student> selectAllStudents() throws DataAccessException {
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Student> students = new ArrayList<>();
        try {
            conn = JDBCUtil.getConnection();
            String sql = new StringBuffer()
                    .append(" select s.id,s.no,s.name,s.age,c.id 'c.id',c.no 'c.no',c.major ")
                    .append(" from t_student s ")
                    .append(" left join t_class c ")
                    .append(" on s.class_id = c.id ")
                    .toString();
            ps = conn.prepareStatement(sql);
            rs = ps.executeQuery();
            while (rs.next()) {
                ClassBean classBean = new ClassBean();
                classBean.setId(rs.getInt("c.id"));
                classBean.setNo(rs.getString("c.no"));
                classBean.setMajor(rs.getString("major"));

                Student student = new Student();
                student.setId(rs.getInt("id"));
                student.setNo(rs.getString("no"));
                student.setName(rs.getString("name"));
                student.setAge(rs.getInt("age"));
                student.setClassBean(classBean);

                students.add(student);
            }
        } catch (DataAccessException e) {
            e.printStackTrace();
            throw e;
        } catch (SQLException e) {
            e.printStackTrace();
            throw new DataAccessException(e);
        } finally {
            JDBCUtil.close(conn, ps, rs);
        }
        return students;
    }

}
